package com.pdg.mcplugin.ranger.dataproviders;

import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.pdg.mcplugin.common.baseclasses.DataProviderBase;
import com.pdg.mcplugin.common.baseclasses.TableProviderBase;
import com.pdg.mcplugin.ranger.Ranger;

public class PublicPlaceNameTableProvider extends TableProviderBase<Ranger,DataProvider,PublicPlaceNameKey,PlaceNameLocation>{

	private static final String TABLE_NAME = "public_place_names";
	private static final String DB_RETRIEVE_PLACE_NAMES = "SELECT `"+
			PublicPlaceNameKey.FIELD_PLACE_NAME+"` FROM `"+
			TABLE_NAME+"` WHERE `"+
			PublicPlaceNameKey.FIELD_WORLD+"`='%s' ORDER BY `"+
			PublicPlaceNameKey.FIELD_PLACE_NAME+"`;";
	private static final String DB_RETRIEVE_FILTERED_PLACE_NAMES = "SELECT `"+
			PublicPlaceNameKey.FIELD_PLACE_NAME+"` FROM `"+
			TABLE_NAME+"` WHERE `"+
			PublicPlaceNameKey.FIELD_WORLD+"`='%s' AND `"+
			PublicPlaceNameKey.FIELD_PLACE_NAME+"` LIKE '%s%%' ORDER BY `"+
			PublicPlaceNameKey.FIELD_PLACE_NAME+"`;";
	private static final String DB_RETRIEVE_PLACE_NAMES_FOR_PLAYER = "SELECT `"+
			PublicPlaceNameKey.FIELD_PLACE_NAME+"`, `"+
			PlaceNameLocation.FIELD_X+"`, `"+
			PlaceNameLocation.FIELD_Y+"`, `"+
			PlaceNameLocation.FIELD_Z+"` FROM `"+
			TABLE_NAME+"` WHERE `"+
			PublicPlaceNameKey.FIELD_WORLD+"`='%s' ORDER BY `"+
			PublicPlaceNameKey.FIELD_PLACE_NAME+"`;";
	private static final String ERROR_COULD_NOT_RETRIEVE_PLACE_NAMES = "Could not retrieve public place names!";
	private static final String ERROR_COULD_NOT_RETRIEVE_FILTERED_PLACE_NAMES = "Could not retrieve filtered public place names!";
	private static final String ERROR_COULD_NOT_RETRIEVE_PLACE_NAMES_FOR_PLAYER = "Could not retrieve public place names!";

	public PublicPlaceNameTableProvider(DataProvider theDataProvider) {
		super(theDataProvider, TABLE_NAME, PublicPlaceNameKey.defaultInstance(), PlaceNameLocation.defaultInstance());
		// TODO Auto-generated constructor stub
	}

	public List<String> retrievePlaceNames(String theWorld) {
		try{
			//create a statement to check for inventory table
			Statement statement = getDataProvider().getConnection().createStatement();
			String theQuery = String.format(DB_RETRIEVE_PLACE_NAMES,
					DataProviderBase.sqlSafeString(theWorld));
			ResultSet result = statement.executeQuery(theQuery);
			List<String> theResult = new ArrayList<String>();
			while(result.next()){
				theResult.add(result.getString(PlaceNameKey.FIELD_PLACE_NAME));
			}
			return theResult;
		}catch(Exception e){
			//an error has occurred
			getLogger().severe(getPlugin().formatMessage(ERROR_COULD_NOT_RETRIEVE_PLACE_NAMES));
			e.printStackTrace();
		}
		return null;
	}

	public List<String> retrieveFilteredPlacesNames(String theWorld,String theFilter) {
		// TODO Auto-generated method stub
		try{
			//create a statement to check for inventory table
			Statement statement = getDataProvider().getConnection().createStatement();
			String theQuery = String.format(DB_RETRIEVE_FILTERED_PLACE_NAMES,
					DataProviderBase.sqlSafeString(theWorld),
					DataProviderBase.sqlSafeString(theFilter));
			ResultSet result = statement.executeQuery(theQuery);
			List<String> theResult = new ArrayList<String>();
			while(result.next()){
				theResult.add(result.getString(PlaceNameKey.FIELD_PLACE_NAME));
			}
			return theResult;
		}catch(Exception e){
			//an error has occurred
			getLogger().severe(getPlugin().formatMessage(ERROR_COULD_NOT_RETRIEVE_FILTERED_PLACE_NAMES));
			e.printStackTrace();
		}
		return null;
	}
	public Map<String, PlaceNameLocation> getPlaceNames(
			String theWorld) {
		try{
			//create a statement to check for inventory table
			Statement statement = getDataProvider().getConnection().createStatement();
			String theQuery = String.format(DB_RETRIEVE_PLACE_NAMES_FOR_PLAYER,
					DataProviderBase.sqlSafeString(theWorld));
			ResultSet result = statement.executeQuery(theQuery);
			Map<String,PlaceNameLocation> theResult = new HashMap<String,PlaceNameLocation>();
			while(result.next()){
				PlaceNameLocation thePlaceNameLocation = new PlaceNameLocation(
						result.getDouble(PlaceNameLocation.FIELD_X), 
						result.getDouble(PlaceNameLocation.FIELD_Y), 
						result.getDouble(PlaceNameLocation.FIELD_Z));
				String thePlaceName = result.getString(PlaceNameKey.FIELD_PLACE_NAME);
				theResult.put(thePlaceName,thePlaceNameLocation);
			}
			return theResult;
		}catch(Exception e){
			//an error has occurred
			getLogger().severe(getPlugin().formatMessage(ERROR_COULD_NOT_RETRIEVE_PLACE_NAMES_FOR_PLAYER));
			e.printStackTrace();
		}
		return null;
	}

}
